<?php
 if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mindex extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function getCount($id_user)
    {
        $sql = "SELECT c.*,DATE_FORMAT(c.created, '%H:%i:%s %d-%m-%Y') AS time, cp.id_place AS id_shop FROM campaign AS c INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign WHERE 1=1 ";
        $sql .= " ORDER BY id DESC";
        
        $xs = $this->db->query($sql)->result_object();
        //echo '<pre>';die(print_r($xs));
        $id = array();
        $array = array();
        $dem = 0;
        $key = '';
        $array_device = array();
        $dem_device = 0;
        $key_device = '';
        
        $array_os = array();
        $dem_os = 0;
        $key_os = '';
        
        foreach($xs AS $el=>$le)
        {
            $id[] = intval($le->id);          
        }
        $data = $this->m_db->where_in('id_campaign', $id)->get('log_access'); // tất cả các campaign luông
        
        foreach($data AS $le=>$el)
        {
            $dem =  $dem + 1;
            if($el['browser_name_group'] == 'Other')
            {
                $key = 'Other';
            }else if(in_array($el['browser_name_group'], array(null, '', '0')))
            {
                $key = 'Other';
            }else{
                $key = '';
                $key = $el['browser_name_group'];
            }
            @$array[$key] = $array[$key] + 1;
        }
        $array['tong_browser'] = $dem;
        asort($array);
        
        foreach($data AS $le=>$el)
        {
            $dem_device =  $dem_device + 1;
            if($el['device_name'] == 'Other')
            {
                $key_device = 'Máy tính';
            }else{
                $key_device = 'Di động';
            }
            @$array_device[$key_device] = $array_device[$key_device] + 1;
        }
        $array_device['tong_device'] = $dem_device;
        asort($array_device);
        
        foreach($data AS $le=>$el)
        {
            $dem_os =  $dem_os + 1;
            if(in_array($el['os_group'], array(null, '', '0')))
            {
                $key_os = 'Other';
            }else{
                $key_os = '';
                $key_os = $el['os_group'];
            }
            @$array_os[$key_os] = $array_os[$key_os] + 1;
        }
        $array_os['tong_os'] = $dem_os;
        asort($array_os);
        
        return (object)array('browser' => $array,'device' => $array_device, 'os' => $array_os);
    }
    
    
    public function getPlace()
    {
        $timeout = $this->db->query('SELECT content FROM `option` WHERE name ="timeout"')->row_object()->content; $timeout = (int)$timeout;
        //die('-'.$timeout);
        $place_data = $this->db->query('SELECT s.id, s.shopName AS placeName, s.address, s.lat, s.`long`, s.logo, m.time, m.info_ram, m.info_cpu, m.info_users FROM shop AS s JOIN modem AS m ON s.MaC = m.nas_id WHERE 1=1
        ')->result_object();
        //echo '<pre>';die(print_r($place_data));
        $arr = $place_data;
        foreach($place_data AS $el=>$le)
        {
            $data_ = $this->db->query('SELECT c.id AS c_id, cp.id_place AS p_id, c.title FROM campaign AS c INNER JOIN campaign_place AS cp ON cp.id_campaign = c.id WHERE (c.active >= 2 AND c.active != 4 ) AND cp.id_place = '.$le->id)->result_object();
            if ( ($le->time)+ $timeout > time()){
                $arr[$el]->status_modem = 'Đang hoạt động';
                $arr[$el]->icon_map = 'public/theme/analytics/images/map-maker-run.png';
            }else{
                $arr[$el]->status_modem = 'Ngừng hoạt động';
                $arr[$el]->icon_map = 'public/theme/analytics/images/map-maker-stop.png';
            }
			foreach( $data_ AS $al=>$la ){
				$data_[$al]->encode_pid = base64_encode($this->encrypt->encode($la->p_id, 'statistic'));
				$data_[$al]->encode_cid = base64_encode($this->encrypt->encode($la->c_id, 'statistic'));
			}
			$arr[$el]->campaign = $data_;
			
			
        }
        //echo '<pre>';die(print_r($arr));
        return $arr;
    }
     
    
    public function getStatic($arr)
    {
        //die(print_r($arr));
        //$array = array(2,1);//die(print_r($array));//
        //$array = explode(',',$arr);die(print_r($array));
        
        $computer = $this->m_db->where_in('id_campaign', $arr)->where(array('device_name' => 'Other'))->count('log_access');//->count_where();
        $mobile = $this->m_db->where_in('id_campaign', $arr)->where_ne('device_name', 'Other')->count('log_access');//->count_where();
        return (object)array('computer' => $computer, 'mobile' => $mobile);
    }
    
    
    public function getSelectList()
    {
        $data = $this->db->query('SELECT * FROM email_receive GROUP BY list_s ORDER BY list_s ASC')->result_object();
        $html  = '';
        foreach($data AS $el=>$le)
        {
            $html .= '<option value="'.$le->list_s.'">'.$le->list_s.'</option>';
        }
        return $html;
    }
    
   public function getNotificationDB($number,$offset,$id_user)
    {
        $limit = $offset ? intval($offset) : '0';
        $id = array();
        
        $sql = "SELECT *,DATE_FORMAT(created, '%H:%i:%s %d-%m-%Y') AS time FROM notification WHERE 1=1 AND id_customers = ".$id_user;
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND title like '%".$key."%' ";
        }
        $sql .= " ORDER BY id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count, 'idCampaign' => $id);
        
    }
    
    public function getdetailNotification($id, $id_user)
    {
        $this->db->update('notification', array('read' => 1), "id = ".$id);
        return $this->db->query('SELECT n.*, c.username, c.fullname,DATE_FORMAT(n.created, "%H:%i:%s %d-%m-%Y") AS time FROM notification AS n INNER JOIN customers AS c ON c.id = n.id_admin WHERE n.id = '.$id.' AND n.id_customers = '.$id_user)->row_object();
    }
    
    public function getCampaignReport()
    {
        $sql = "SELECT c.id,c.code_campaign,c.id_customers,c.title AS campaign_title,c.active, c.id_agency, c.link_campaign, c.status_old, c.id_update, c.created, u.fullname AS agency_fullname, CONCAT('<b>',s.shopName,'</b>',' - ',s.address) AS nameplace, s.id AS id_shop FROM campaign AS c LEFT JOIN `user` AS u ON u.id = c.id_agency INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign INNER JOIN shop AS s ON cp.id_place = s.id WHERE c.active != 4 ";
        $sql .= " ORDER BY c.id DESC LIMIT 0,3";
        $xs = $this->db->query($sql)->result_object();
        //echo '<pre>';die(print_r($xs));
        foreach ( $xs AS $el=>$le ){
             $dem = 0;
            $dem_user = 0;
            $tmp = array();
            $tmp['mac'] = '';
            $total = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => $le->id_shop ))->get('log_access');
            foreach($total AS $ell=>$lle)
            {
                // Đếm tổng
                $dem = $dem + 1;
                if($lle['mac'] != $tmp['mac'])
                {
                    // số người dùng
                    $tmp['mac'] = $lle['mac'];
                    $dem_user = $dem_user + 1;
                }
            }
            
            $xs[$el]->total_visit = $dem;
            $xs[$el]->user_visit = $dem_user;
            
            $total_facebook = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_facebook');
            $total_mail = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mail');
            $total_mobile = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mobile');
            $click = $total_facebook + $total_mail + $total_mobile;
            
            @$outClick = round((($dem - $click) / $dem) * 100);
            @$_click = round(($click / $dem) * 100);
            
            $xs[$el]->personNoClick = $outClick;
            $xs[$el]->personClick = $_click;
            
            /** get tong thơi zan trang dic và so lan hien thi trang dic*/
            $_data_landing_page = $this->db->query('SELECT landing_page, time_landing_page, welcome_page, time_welcome_page FROM campaign WHERE id = '.intval($le->id))->row_object();
            $xs[$el]->time_landing_page = $_data_landing_page->time_landing_page;
            $xs[$el]->landing_page = $_data_landing_page->landing_page;
            $xs[$el]->welcome_page = $_data_landing_page->welcome_page;
            $xs[$el]->time_welcome_page = $_data_landing_page->time_welcome_page;
        }
        return array('data' => $xs);
    }
    
    
    public function ajaxGetCampaignReport($last_id)
    {
        $sql = "SELECT c.id,c.code_campaign,c.id_customers,c.title AS campaign_title,c.active, c.id_agency, c.link_campaign, c.status_old, c.id_update, c.created, u.fullname AS agency_fullname, CONCAT('<b>',s.shopName,'</b>',' - ',s.address) AS nameplace, s.id AS id_shop FROM campaign AS c LEFT JOIN `user` AS u ON u.id = c.id_agency INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign INNER JOIN shop AS s ON cp.id_place = s.id WHERE c.active != 4 ";
        $sql .= " ORDER BY c.id DESC LIMIT $last_id,3";
        $xs = $this->db->query($sql)->result_object();
        foreach ( $xs AS $el=>$le ){
             $dem = 0;
            $dem_user = 0;
            $tmp = array();
            $tmp['mac'] = '';
            $total = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => $le->id_shop ))->get('log_access');
            foreach($total AS $ell=>$lle)
            {
                // Đếm tổng
                $dem = $dem + 1;
                if($lle['mac'] != $tmp['mac'])
                {
                    // số người dùng
                    $tmp['mac'] = $lle['mac'];
                    $dem_user = $dem_user + 1;
                }
            }
            
            $xs[$el]->total_visit = $dem;
            $xs[$el]->user_visit = $dem_user;
            
            $total_facebook = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_facebook');
            $total_mail = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mail');
            $total_mobile = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mobile');
            $click = $total_facebook + $total_mail + $total_mobile;
            
            @$outClick = round((($dem - $click) / $dem) * 100);
            @$_click = round(($click / $dem) * 100);
            
            $xs[$el]->personNoClick = $outClick;
            $xs[$el]->personClick = $_click;
            
            /** get tong thơi zan trang dic và so lan hien thi trang dic*/
             $_data_landing_page = $this->db->query('SELECT landing_page, time_landing_page, welcome_page, time_welcome_page FROM campaign WHERE id = '.intval($le->id))->row_object();
            $xs[$el]->time_landing_page = $_data_landing_page->time_landing_page;
            $xs[$el]->landing_page = $_data_landing_page->landing_page;
            $xs[$el]->welcome_page = $_data_landing_page->welcome_page;
            $xs[$el]->time_welcome_page = $_data_landing_page->time_welcome_page;
        }
        return array('data' => $xs);
    }
    
    public function getReportExport()
    {
        $sql = "SELECT c.id,c.code_campaign,c.id_customers,c.title AS campaign_title,c.active, c.id_agency, c.link_campaign, c.status_old, c.id_update, c.created, u.fullname AS agency_fullname, CONCAT('<b>',s.shopName,'</b>',' - ',s.address) AS nameplace, s.id AS id_shop FROM campaign AS c LEFT JOIN `user` AS u ON u.id = c.id_agency INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign INNER JOIN shop AS s ON cp.id_place = s.id WHERE c.active != 4 ";
        $sql .= " ORDER BY c.id DESC";
        $xs = $this->db->query($sql)->result_object();
        foreach ( $xs AS $el=>$le ){
            $dem = 0;
            $dem_user = 0;
            $tmp = array();
            $tmp['mac'] = '';
            $total = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => $le->id_shop ))->get('log_access');
            foreach($total AS $ell=>$lle)
            {
                // Đếm tổng
                $dem = $dem + 1;
                if($lle['mac'] != $tmp['mac'])
                {
                    // số người dùng
                    $tmp['mac'] = $lle['mac'];
                    $dem_user = $dem_user + 1;
                }
            }
            
            $xs[$el]->total_visit = $dem;
            $xs[$el]->user_visit = $dem_user;
            
            $total_facebook = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_facebook');
            $total_mail = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mail');
            $total_mobile = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mobile');
            $click = $total_facebook + $total_mail + $total_mobile;
            
            @$outClick = round((($dem - $click) / $dem) * 100);
            @$_click = round(($click / $dem) * 100);
            
            $xs[$el]->personNoClick = $outClick;
            $xs[$el]->personClick = $_click;
            
            /** get tong thơi zan trang dic và so lan hien thi trang dic*/
            $_data_landing_page = $this->db->query('SELECT landing_page, time_landing_page, welcome_page, time_welcome_page FROM campaign WHERE id = '.intval($le->id))->row_object();
            $xs[$el]->time_landing_page = $_data_landing_page->time_landing_page;
            $xs[$el]->landing_page = $_data_landing_page->landing_page;
            $xs[$el]->welcome_page = $_data_landing_page->welcome_page;
            $xs[$el]->time_welcome_page = $_data_landing_page->time_welcome_page;
        }
        return array('data' => $xs);
    }
    
    public function getAjaxReportExport($times,$times_start,$times_end,$agency_id,$customer_id)
    {
        $sql = "SELECT c.id,c.code_campaign,c.id_customers,c.title AS campaign_title,c.active, c.id_agency, c.link_campaign, c.status_old, c.id_update, c.created, u.fullname AS agency_fullname, CONCAT('<b>',s.shopName,'</b>',' - ',s.address) AS nameplace, s.id AS id_shop FROM campaign AS c LEFT JOIN `user` AS u ON u.id = c.id_agency INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign INNER JOIN shop AS s ON cp.id_place = s.id WHERE c.active != 4 ";
        if(!in_array($times_start, array(null,'','0',0))){
            $sql .= " AND (c.time_start BETWEEN '$times_start' AND '$times_end') OR (c.time_end BETWEEN '$times_start' AND '$times_end') OR ('$times_start' AND '$times_end' BETWEEN c.time_start AND c.time_end)";   
        }
        if(!in_array($agency_id, array(null,'','0',0))){
            $sql .= " AND c.id_agency = ".$agency_id;
        }
        if(!in_array($customer_id, array(null,'','0',0))){
            $sql .= " AND c.id_customers = ".$customer_id;
        }
        $sql .= " ORDER BY c.id DESC";
        $xs = $this->db->query($sql)->result_object();
        
        if(!in_array($times, array(null, '', '0')))
        {
            $exp = explode('|',$times);
            $time_1 = new MongoInt64($exp[0]);
            $time_2 = new MongoInt64($exp[1]);
            
            
            foreach ( $xs AS $el=>$le ){
                $dem = 0;
                $dem_user = 0;
                $tmp = array();
                $tmp['mac'] = '';
                $total = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => $le->id_shop ))->where_gt('time_vs',$time_1)->where_lt('time_vs',$time_2)->get('log_access');
                
                foreach($total AS $ell=>$lle)
                {
                    // Đếm tổng
                    $dem = $dem + 1;
                    if($lle['mac'] != $tmp['mac'])
                    {
                        // số người dùng
                        $tmp['mac'] = $lle['mac'];
                        $dem_user = $dem_user + 1;
                    }
                }
                
                $xs[$el]->total_visit = $dem;
                $xs[$el]->user_visit = $dem_user;
                
                $total_facebook = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->where_gt('time_vs',$time_1)->where_lt('time_vs',$time_2)->count('log_facebook');
                $total_mail = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->where_gt('time_vs',$time_1)->where_lt('time_vs',$time_2)->count('log_mail');
                $total_mobile = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->where_gt('time_vs',$time_1)->where_lt('time_vs',$time_2)->count('log_mobile');
                $click = $total_facebook + $total_mail + $total_mobile;
                
                @$outClick = round((($dem - $click) / $dem) * 100);
                @$_click = round(($click / $dem) * 100);
                
                $xs[$el]->personNoClick = $outClick;
                $xs[$el]->personClick = $_click;
                
                /** get tong thơi zan trang dic và so lan hien thi trang dic*/
                $_data_landing_page = $this->db->query('SELECT landing_page, time_landing_page, welcome_page, time_welcome_page FROM campaign WHERE id = '.intval($le->id))->row_object();
                $xs[$el]->time_landing_page = $_data_landing_page->time_landing_page;
                $xs[$el]->landing_page = $_data_landing_page->landing_page;
                $xs[$el]->welcome_page = $_data_landing_page->welcome_page;
                $xs[$el]->time_welcome_page = $_data_landing_page->time_welcome_page;
            }
        }else{
            foreach ( $xs AS $el=>$le ){
                $dem = 0;
                $dem_user = 0;
                $tmp = array();
                $tmp['mac'] = '';
                $total = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => $le->id_shop ))->get('log_access');
                foreach($total AS $ell=>$lle)
                {
                    // Đếm tổng
                    $dem = $dem + 1;
                    if($lle['mac'] != $tmp['mac'])
                    {
                        // số người dùng
                        $tmp['mac'] = $lle['mac'];
                        $dem_user = $dem_user + 1;
                    }
                }
                
                $xs[$el]->total_visit = $dem;
                $xs[$el]->user_visit = $dem_user;
                
                $total_facebook = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_facebook');
                $total_mail = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mail');
                $total_mobile = $this->m_db->where(array('id_campaign' => intval($le->id), 'id_shop' => intval($le->id_shop) ))->count('log_mobile');
                $click = $total_facebook + $total_mail + $total_mobile;
                
                @$outClick = round((($dem - $click) / $dem) * 100);
                @$_click = round(($click / $dem) * 100);
                
                $xs[$el]->personNoClick = $outClick;
                $xs[$el]->personClick = $_click;
                
                /** get tong thơi zan trang dic và so lan hien thi trang dic*/
                $_data_landing_page = $this->db->query('SELECT landing_page, time_landing_page, welcome_page, time_welcome_page FROM campaign WHERE id = '.intval($le->id))->row_object();
                $xs[$el]->time_landing_page = $_data_landing_page->time_landing_page;
                $xs[$el]->landing_page = $_data_landing_page->landing_page;
                $xs[$el]->welcome_page = $_data_landing_page->welcome_page;
                $xs[$el]->time_welcome_page = $_data_landing_page->time_welcome_page;
            }
        }
        
        return array('data' => $xs);
    }
    
    public function getListAgency()
    {
        $data = $this->db->query("SELECT id,username,fullname FROM user WHERE level = 2")->result_array();
        return $data;
    }
    
    public function getListCustomers()
    {
        $data = $this->db->query("SELECT id,username,fullname FROM user WHERE level = 3")->result_array();
        return $data;
    }
    
    public function ajaxGetListCustomers($agency_id)
    {
        if(!in_array($agency_id, array(null, '', '0', 0))){
            $data = $this->db->query("SELECT id,username,fullname FROM user WHERE agency = ".$agency_id)->result_array();
        }else{
            $data = $this->db->query("SELECT id,username,fullname FROM user WHERE level = 3")->result_array();
        }
        return $data;
    }
    
    public function getModem($nas_id,$info_ram,$info_cpu,$info_users){
        $db = $this->db->query('SELECT * FROM `modem` WHERE nas_id = "'.$nas_id.'" LIMIT 1')->num_rows();
        if( $db == 0 ){
            $data = array( 'data' => 201, 'content' => 'Modem không tồn tại trong hệ thống' );
        }else{
            $this->db->update('modem', array( 'time' => time(), 'info_ram' => $info_ram, 'info_cpu' => $info_cpu, 'info_users' => $info_users  ), "nas_id = '".$nas_id."'");
            $data = array( 'data' => 200, 'content' => 'Cập nhật thành công' );
        }
        return $data;
    }
    
}